﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using personalplaner.business.stamm;
using System.Data;
using personalplaner.core.sql;
using personalplaner.business.common;

namespace personalplaner.dataprovider.services
{
	public partial class AbteilungDataService
	{
		#region Get Methods

		/// <summary>
		/// Creates a DataView with all Abteilungen. [abteilungid,name,einsatzprotag]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="mandid">mandid</param>
		/// <param name="abteilungid">abteilung id</param>
		/// <param name="addEmptyRow">add an empty row at the beginnig of the list</param>
		/// <returns>abteilungid,name,einsatzprotag</returns>
		public IEnumerable<Abteilung> GetAbteilungen(DatabaseSettings settings, string mandid = "", int abteilungid = 0, bool addEmptyRow = false)
		{
			var query = new Querry(settings);
			query.SetSTPName("get_abteilungen");
			query.AddParameter("@mandid", mandid);
			query.AddParameter("@abteilungid", abteilungid);

			var table = query.ExecuteForDataTable();

			//if (table.Rows.Count == 0)
			//    return null;

			try
			{
				var lst = new List<Abteilung>(m_abteilungenData(table));

				if (addEmptyRow)
				{
					lst.Insert(0, new Abteilung());
				}

				return lst;
			}
			catch (Exception e)
			{
				throw new Exception("GetAbteilungen caused an error", e);
			}
		}

		/// <summary>
		/// [AbteilungID, Name, Prozent]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="mandid"></param>
		/// <param name="prsid"></param>
		/// <param name="date"></param>
		/// <param name="addEmptyRow"></param>
		/// <param name="assigned"></param>
		/// <returns></returns>
        public IEnumerable<PersonAbteilung> GetPersonAbteilungen(DatabaseSettings settings, string mandid, int prsid, bool assigned, DateTime date, bool addEmptyRow = false)
        {
            DataTable table;

            //var str = string.Format("select abteilungid=AbteilungID, name=Name from Abteilung where mandid = '{0}' and AbteilungID not in (select AbteilungID from PersonalAbteilung where PrsId = {1} and isnull(Status,1) = 1)", mandid, prsid);
            //if (assigned)
            //    str = string.Format("select abteilungid=p.AbteilungID,name=a.Name, einsatzprotag=p.Prozent, prozent=p.Prozent from PersonalAbteilung p,Abteilung a where p.AbteilungID = a.AbteilungID and p.PrsId = {0} and isnull(p.Status,1) = 1", prsid);
            var str = string.Format("select abteilungid=AbteilungID, name=Name from Abteilung where mandid = '{0}' and AbteilungID not in (select AbteilungID from PersonalAbteilung where PrsId = {1} and Von <= '{2}' and Bis >= '{2}')", mandid, prsid, date.ToString("yyyyMMdd"));
            if (assigned)
                str = string.Format("select abteilungid=p.AbteilungID,name=a.Name, einsatzprotag=p.Prozent, prozent=p.Prozent from PersonalAbteilung p,Abteilung a where p.AbteilungID = a.AbteilungID and p.PrsId = {0} and Von <= '{1}' and Bis >= '{1}'", prsid, date.ToString("yyyyMMdd"));

            table = SimpleQuerry.ExecuteForDataTable(settings, str);

            try
            {
                var lst = new List<PersonAbteilung>(m_personAbteilungenData(table));

                if (addEmptyRow)
                {
                    lst.Insert(0, new PersonAbteilung());
                }

                return lst;
            }
            catch (Exception e)
            {
                throw new Exception("GetAbteilungen caused an error", e);
            }
        }

		/// <summary>
		/// ehemals DataView GetPersonAbteilungHistory(int prsid)
		/// [PrsId,AbteilungID,Name,Von,Bis,Prozent,Status,Statustext]
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="prsid"></param>
		/// <returns></returns>
		public IEnumerable<PersonAbteilung> GetPersonAbteilungen(DatabaseSettings settings, int prsid, DateTime date)
		{
            //var str = string.Format("select prsid=PrsId,abteilungid=a.AbteilungID,name=a.Name,von=Von,bis=Bis,prozent=Prozent,status=isnull(Status,1),statustext=case when Status=1 then 'Aktiv' else 'Inaktiv'end from PersonalAbteilung pa inner join Abteilung a on pa.AbteilungID=a.AbteilungID where PrsId = {0} order by Von desc, Bis", prsid);
            var str = string.Format("select prsid=PrsId,abteilungid=a.AbteilungID,name=a.Name,von=Von,bis=Bis,prozent=Prozent,status=isnull(Status,1),statustext=case when Von <= '{1}' and Bis >= '{1}' then 'Aktiv' else 'Inaktiv'end from PersonalAbteilung pa inner join Abteilung a on pa.AbteilungID=a.AbteilungID where PrsId = {0} order by Von desc, Bis", prsid, date.ToString("yyyyMMdd"));
			var table = SimpleQuerry.ExecuteForDataTable(settings, str);

			try
			{
				return m_personAbteilungenData(table);
			}
			catch (Exception e)
			{
				throw new Exception("GetPersonAbteilungen caused an error", e);
			}
		}

		public bool GetIsAbteilungUsed(DatabaseSettings settings, int abteilungid)
		{
			var dv = SimpleQuerry.ExecuteForDataTable(settings, "select AbteilungID from ArbeitsPlan ap inner join PlanSchema s on s.SchemaID = ap.SchemaID where AbteilungID = " + abteilungid + " group by AbteilungID");
			if (dv.Rows.Count > 0)
				return true;

			return false;
		}

        public int GetAbteilungId(DatabaseSettings settings, string abteilung)
        {
            var str = string.Format("select AbteilungID from Abteilung where Name = '{0}'", abteilung);
            var table = SimpleQuerry.ExecuteForDataTable(settings, str);

            if (table.Rows.Count == 0)
                return 0;

            try
            {
                return table.Rows[0].Field<int>("AbteilungID");
            }
            catch (Exception e)
            {
                throw new Exception("GetAbteilungen caused an error", e);
            }
        }

		#endregion

		#region Set Methods

        /// <summary>
        /// adds a abteilung to the person with the prsid
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="prsid"></param>
        /// <param name="abteilungid"></param>
        /// <param name="von"></param>
        /// <param name="bis"></param>
        /// <param name="prozent"></param>
        /// <returns></returns>
		public bool SetPersonAbteilung(DatabaseSettings settings, int prsid, int abteilungid, DateTime von, DateTime bis, int prozent)
		{
			try
			{
				var stp = new Querry(settings, "set_prsabteilung");

				stp.AddParameter("@abteilungid", abteilungid);
				stp.AddParameter("@prsid", prsid);
				stp.AddParameter("@von", von.ToString("yyyyMMdd"));
				stp.AddParameter("@bis", bis.ToString("yyyyMMdd"));
				stp.AddParameter("@prozent", prozent);

				return stp.Execute(true);
			}
			catch (Exception e)
			{
                Console.Write(e);

				return false;
			}
		}

        /// <summary>
        /// adds a new abteilung
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="abteilung"></param>
        /// <param name="mandid"></param>
        /// <returns></returns>
		public bool SetAbteilung(DatabaseSettings settings, Abteilung abteilung, string mandid)
		{
			var stp = new Querry(settings, "set_abteilung");

			stp.AddParameter("@abteilungname", abteilung.Name);
			stp.AddParameter("@kurztext", abteilung.Name);
			stp.AddParameter("@beschreibung", abteilung.Beschreibung);
			stp.AddParameter("@stundenprotag", abteilung.EinsaetzeProTag);
			stp.AddParameter("@mandid", mandid);

			return stp.Execute(true);
		}

		#endregion

		#region Update Methods

		public bool UpdateAbteilung(DatabaseSettings settings, Abteilung abteilung)
		{
			var stp = new Querry(settings, "upd_abteilung");

			stp.AddParameter("@abteilungid", abteilung.AbteilungID);
			stp.AddParameter("@abteilungname", abteilung.Name);
			stp.AddParameter("@beschreibung", abteilung.Beschreibung);
			stp.AddParameter("@stundenprotag", abteilung.EinsaetzeProTag);

			return stp.Execute(true);
		}

		#endregion

		#region Delete Methods

		public bool RemovePersonAbteilung(DatabaseSettings settings, int prsid, int abteilungid, DateTime datevon, DateTime datebis)
		{
			try
			{
				var stp = new Querry(settings, "upd_prsabteilung");

				stp.AddParameter("@abteilungid", abteilungid);
				stp.AddParameter("@prsid", prsid);
				stp.AddParameter("@vonnew", datevon.ToString("yyyyMMdd"));
				stp.AddParameter("@bisnew", datebis.ToString("yyyyMMdd"));

				return stp.Execute(true);
			}
			catch (Exception)
			{
				return false;
			}
		}

        public bool DeletePersonAbteilung(DatabaseSettings settings, int prsid, int abteilungid, DateTime datevon, DateTime datebis)
        {
            try
            {
                var stp = new Querry(settings, "del_prsabteilung");

                stp.AddParameter("@abteilungid", abteilungid);
                stp.AddParameter("@prsid", prsid);
                stp.AddParameter("@vonnew", datevon.ToString("yyyyMMdd"));
                stp.AddParameter("@bisnew", datebis.ToString("yyyyMMdd"));

                return stp.Execute(true);
            }
            catch (Exception)
            {
                return false;
            }
        }

		public bool DeleteAbteilung(DatabaseSettings settings, int abteilungid)
		{
			if (!GetIsAbteilungUsed(settings, abteilungid))
			{
				var stp = new Querry(settings, "del_abteilung");
				stp.AddParameter("@abteilungid", abteilungid);

				return stp.Execute(true);
			}

			return false;
		}

		#endregion
    }
}
